﻿CREATE TRIGGER [dbo].[tru_Payment]
	ON  dbo.t_Payment
AFTER UPDATE
AS 
SET NOCOUNT ON;


IF TRIGGER_NESTLEVEL(OBJECT_ID('triu_Payment_StatusID')) >0 RETURN

IF @@ROWCOUNT > 1 BEGIN
	RAISERROR('Update of multiple payments is not supported.',16,1)
	ROLLBACK
	RETURN
END

IF NOT(UPDATE(Amount) OR UPDATE(DatePaid) OR UPDATE(StatusID)) RETURN

IF EXISTS(SELECT * FROM inserted i INNER JOIN deleted d ON i.ID = d.ID AND (0+dbo.fs_StateParams_IsRecalcPrinBal(i.StatusID) + dbo.fs_StateParams_IsRecalcPrinBal(d.StatusID)) = 0)BEGIN
	PRINT OBJECT_NAME(@@PROCID)+': nothing to recalc'
	RETURN
END


PRINT OBJECT_NAME(@@PROCID)

DECLARE @Deleted_ TABLE(ID int,LoanID int,Amount numeric(18,2),DatePaid datetime,WaterFallID int,StatusID int)
DECLARE @Deleted TABLE(ID int,LoanID int,Amount numeric(18,2),DatePaid datetime,WaterFallID int,StatusID int)

DECLARE @CI int EXEC @CI = sp__Context_SecurityOff 1
EXECUTE AS Login='AFC-HOME\SQLServer'

DELETE t_Payment
OUTPUT deleted.ID,deleted.LoanID,deleted.Amount,deleted.DatePaid,deleted.WaterFallID,deleted.StatusID INTO @Deleted_
FROM t_Payment P INNER JOIN deleted D ON P.LoanID = D.LoanID AND P.ID >= D.ID

INSERT INTO @Deleted SELECT * FROM @Deleted_ ORDER BY DatePaid, ID
SELECT 'trd_Deleted'D,* FROM @Deleted

WHILE EXISTS(SELECT * FROM @Deleted) BEGIN
	INSERT INTO v_Payment#ForInsert(LoanID,Amount,DatePaid,WaterFallID,StatusID)
	SELECT TOP (1) LoanID,Amount,DatePaid,WaterFallID,StatusID FROM @Deleted
	DELETE TOP(1) FROM @Deleted
END

REVERT
EXEC sp__Context_SecurityOff @CI

GO
DISABLE TRIGGER [dbo].[tru_Payment]
    ON [dbo].[t_Payment];

